package com.foodorder.repository;

import com.foodorder.entity.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 商品数据访问层 - 增强版
 */
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
    
    /**
     * 根据店铺ID查找商品（分页）
     */
    Page<Product> findByStoreIdAndStatus(Long storeId, Product.ProductStatus status, Pageable pageable);
    
    /**
     * 根据分类ID查找商品
     */
    Page<Product> findByCategoryIdAndStatus(Long categoryId, Product.ProductStatus status, Pageable pageable);
    
    /**
     * 根据店铺ID和分类ID查找商品
     */
    Page<Product> findByStoreIdAndCategoryIdAndStatus(Long storeId, Long categoryId, Product.ProductStatus status, Pageable pageable);
    
    /**
     * 根据店铺ID和分类ID查找商品（返回List）
     */
    List<Product> findByStoreIdAndCategoryIdAndStatus(Long storeId, Long categoryId, Product.ProductStatus status);
    
    /**
     * 商品名称模糊搜索
     */
    @Query("SELECT p FROM Product p WHERE p.name LIKE %:keyword% AND p.status = :status")
    Page<Product> findByNameContainingAndStatus(@Param("keyword") String keyword, @Param("status") Product.ProductStatus status, Pageable pageable);
    
    /**
     * 根据价格范围查找商品
     */
    @Query("SELECT p FROM Product p WHERE p.price BETWEEN :minPrice AND :maxPrice AND p.status = :status")
    Page<Product> findByPriceRangeAndStatus(@Param("minPrice") BigDecimal minPrice, @Param("maxPrice") BigDecimal maxPrice, @Param("status") Product.ProductStatus status, Pageable pageable);
    
    /**
     * 查找热销商品（按销量排序）
     */
    @Query("SELECT p FROM Product p WHERE p.status = :status ORDER BY p.sales DESC")
    Page<Product> findHotProducts(@Param("status") Product.ProductStatus status, Pageable pageable);
    
    /**
     * 查找推荐商品
     */
    @Query("SELECT p FROM Product p WHERE p.recommended = true AND p.status = :status ORDER BY p.createdAt DESC")
    Page<Product> findRecommendedProducts(@Param("status") Product.ProductStatus status, Pageable pageable);
    
    /**
     * 根据店铺ID统计商品数量
     */
    @Query("SELECT COUNT(p) FROM Product p WHERE p.storeId = :storeId AND p.status = :status")
    long countByStoreIdAndStatus(@Param("storeId") Long storeId, @Param("status") Product.ProductStatus status);
    
    /**
     * 更新商品销量 - 原子操作
     */
    @Modifying
    @Transactional
    @Query("UPDATE Product p SET p.sales = p.sales + :quantity WHERE p.id = :productId")
    int updateSales(@Param("productId") Long productId, @Param("quantity") Integer quantity);
    
    /**
     * 原子减少库存 - 带版本号的乐观锁
     */
    @Modifying
    @Transactional
    @Query("UPDATE Product p SET p.stock = p.stock - :quantity, p.version = p.version + 1 " +
           "WHERE p.id = :productId AND p.stock >= :quantity AND p.stock = :currentStock")
    int updateStockWithVersion(@Param("productId") Long productId, 
                              @Param("quantity") Integer quantity, 
                              @Param("currentStock") Integer currentStock);
    
    /**
     * 原子增加库存 - 用于取消订单
     */
    @Modifying
    @Transactional
    @Query("UPDATE Product p SET p.stock = p.stock + :quantity WHERE p.id = :productId")
    int increaseStock(@Param("productId") Long productId, @Param("quantity") Integer quantity);
    
    /**
     * 批量更新商品状态
     */
    @Modifying
    @Transactional
    @Query("UPDATE Product p SET p.status = :status WHERE p.id IN :productIds")
    int batchUpdateStatus(@Param("productIds") List<Long> productIds, @Param("status") Product.ProductStatus status);
    
    /**
     * 检查库存充足的商品
     */
    @Query("SELECT p FROM Product p WHERE p.id IN :productIds AND p.stock >= :minStock")
    List<Product> findProductsWithSufficientStock(@Param("productIds") List<Long> productIds, @Param("minStock") Integer minStock);
    
    /**
     * 查找库存不足的商品
     */
    @Query("SELECT p FROM Product p WHERE p.stock <= :threshold AND p.status = :status")
    List<Product> findLowStockProducts(@Param("threshold") Integer threshold, @Param("status") Product.ProductStatus status);
    
    /**
     * 根据分类ID查找商品数量
     */
    long countByCategoryId(Long categoryId);
    
    /**
     * 查找指定店铺的所有分类
     */
    @Query("SELECT DISTINCT p.categoryId FROM Product p WHERE p.storeId = :storeId")
    List<Long> findDistinctCategoriesByStoreId(@Param("storeId") Long storeId);
    
    /**
     * 软删除商品（更新状态为DELETED）
     */
    @Modifying
    @Transactional
    @Query("UPDATE Product p SET p.status = com.foodorder.entity.Product$ProductStatus.OFFLINE WHERE p.id = :productId")
    int softDeleteProduct(@Param("productId") Long productId);
    
    /**
     * 恢复软删除的商品
     */
    @Modifying
    @Transactional
    @Query("UPDATE Product p SET p.status = com.foodorder.entity.Product$ProductStatus.AVAILABLE WHERE p.id = :productId AND p.status = com.foodorder.entity.Product$ProductStatus.OFFLINE")
    int restoreProduct(@Param("productId") Long productId);
    
    /**
     * 批量更新商品价格
     */
    @Modifying
    @Transactional
    @Query("UPDATE Product p SET p.price = :newPrice WHERE p.id IN :productIds")
    int batchUpdatePrice(@Param("productIds") List<Long> productIds, @Param("newPrice") BigDecimal newPrice);
    
    /**
     * 查找即将售罄的商品（库存低于警戒线）
     */
    @Query("SELECT p FROM Product p WHERE p.stock > 0 AND p.stock <= :warningThreshold AND p.status = com.foodorder.entity.Product$ProductStatus.AVAILABLE")
    List<Product> findAlmostSoldOutProducts(@Param("warningThreshold") Integer warningThreshold);
    
    /**
     * 根据店铺ID和状态查找商品
     */
    List<Product> findByStoreIdAndStatus(Long storeId, Product.ProductStatus status);
    
    /**
     * 查找特定时间段内的热门商品
     */
    @Query("SELECT p FROM Product p WHERE p.status = com.foodorder.entity.Product$ProductStatus.AVAILABLE AND p.sales > :minSales ORDER BY p.sales DESC")
    List<Product> findPopularProducts(@Param("minSales") Integer minSales, Pageable pageable);
    
    /**
     * 获取商品的当前版本号（用于乐观锁）
     */
    @Query("SELECT p.version FROM Product p WHERE p.id = :productId")
    Optional<Integer> getProductVersion(@Param("productId") Long productId);
    
    /**
     * 检查商品是否存在且可用
     */
    @Query("SELECT COUNT(p) > 0 FROM Product p WHERE p.id = :productId AND p.status = com.foodorder.entity.Product$ProductStatus.AVAILABLE")
    boolean isProductAvailable(@Param("productId") Long productId);
    
    /**
     * 根据店铺ID和推荐状态查找商品
     */
    List<Product> findByStoreIdAndRecommendedAndStatus(Long storeId, boolean recommended, Product.ProductStatus status);
    
    /**
     * 搜索商品（通用搜索方法）
     */
    @Query("SELECT p FROM Product p WHERE p.name LIKE %:keyword% AND p.status = com.foodorder.entity.Product$ProductStatus.AVAILABLE")
    Page<Product> searchProducts(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 根据店铺ID查找商品
     */
    List<Product> findByStoreId(Long storeId);
    
    /**
     * 根据店铺ID和状态查找商品，按销量排序
     */
    List<Product> findByStoreIdAndStatusOrderByMonthlySalesDesc(Long storeId, Product.ProductStatus status);
    
    /**
     * 根据店铺ID和创建时间查找商品
     */
    List<Product> findByStoreIdAndCreatedAtAfterOrderByCreatedAtDesc(Long storeId, LocalDateTime createdAfter);
}